
-- exec sp_vendor_reports 'ATK4','0', '0', 'ex500'
--select * from Final_test_lot_History where FL_Current_Status = ''

 ALTER    proc sp_vendor_reports
 @vendor varchar(50),
 @date_From varchar(10),
 @date_To   varchar(10),
 @productName varchar(50) 
 as
 begin
 	declare @SQLString NVARCHAR(4000)
	declare @SQLString1 NVARCHAR(4000)
	set  @SQLString1 = ''

 	declare @ParamDefinition NVARCHAR(500)
 	set @SQLString = N' select  flh.lot_id AS Lot_ID, lt.lot_number AS Lot_Number , ps.PS_Type_Name AS Process_Type, ' 
 	set @SQLString = @SQLString + N' flh.FL_Date_In AS Date_In, flh.FL_Date_Out AS Date_Out, flh.FL_Expected_Date_Out AS Projected_Date, '
 	set @SQLString = @SQLString + N' ISNULL( datediff(day, flh.FL_Date_In, flh.FL_Date_Out ), pslt.PSLT_Cycle_Time_Normal ) AS Actual_Cycle_Time, '
 	set @SQLString = @SQLString + N' ISNULL( datediff(day, flh.FL_Date_In,flh.FL_Expected_Date_Out ), pslt.PSLT_Cycle_Time_Normal ) AS Projected_Cycle_Time, '
 	set @SQLString = @SQLString + N' pslt.PSLT_Cycle_Time_Normal AS Commited_Cycle_Time, flh.FL_Input_Quantity AS Qty_In , null AS  Die_In, '
 	set @SQLString = @SQLString + N' flh.FL_Output_Quantity AS Qty_Out, ISNULL( flh.FL_Yield, 100.0 ) AS Actual_Yield, ISNULL( pslt.PSLT_Expected_Yield, 100.0 ) AS Commited_Yield, '
 	set @SQLString = @SQLString + N' 100 AS Target_Yield, v.v_name AS Vendor_Name, '
	set @SQLString = @SQLString + N' flh.FL_Vendor_Lot_Number AS Vendor_Lot '
 	set @SQLString = @SQLString + N' from fab_lot_history flh , lot_table lt , '
 	set @SQLString = @SQLString + N' process_Step ps,  Location l ,Process_Step_Location_Table pslt, Vendor V , product p '
 	set @SQLString = @SQLString + N' where flh.lot_id = lt.lot_id '
 	set @SQLString = @SQLString + N' and lt.processStep_id = ps.processStep_id '
 	set @SQLString = @SQLString + N' and lt.location_id = l.location_id '
 	set @SQLString = @SQLString + N' and lt.processStep_id = pslt.processStep_id '
 	set @SQLString = @SQLString + N' and lt.location_id = pslt.location_id '
 	set @SQLString = @SQLString + N' and L.Vendor_ID = V.Vendor_ID '
 	set @SQLString = @SQLString + N' and p.processStep_id = lt.processStep_id '
	set @SQLString = @SQLString + N' and fl_Current_Status = ''Close'''
 	
 	if ( @vendor IS NOT NULL  )
 	begin
 	  set @SQLString = @SQLString + ' and V.V_Name = ''' +  @VENDOR + ''''
 	end
 	if ( @date_From IS NOT NULL AND ltrim(rtrim(@date_From)) <> '' AND @date_From <> '0' )
 	begin
 	  set @SQLString = @SQLString + ' and flh.FL_Date_In >= ''' +  @date_From + ''''
 	end
 	if ( @date_To IS NOT NULL AND ltrim(rtrim(@date_To)) <> '' AND @date_To <> '0' )
 	begin
 	  set @SQLString = @SQLString + ' and flh.FL_Date_Out <=  ''' +  @date_To + ''''
 	end
 	if ( @productName IS NOT NULL AND ltrim(rtrim(@productName)) <> '' AND @productName <> '0' )
 	begin
 	  set @SQLString = @SQLString + ' and ( SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
									CASE 
										WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
										WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
									END 
						)  = '''+ @productName +''''
	end
 	
	SET @SQLString = @SQLString + ' UNION '	

	set @SQLString = @SQLString + N' select  slh.lot_id AS Lot_ID, lt.lot_number AS Lot_Number, ps.PS_Type_Name AS Process_Type, ' 
 	set @SQLString = @SQLString + N' slh.SL_Date_In AS Date_In, slh.SL_Date_Out AS Date_Out, ISNULL( slh.SL_Expected_Date_Out, slh.SL_Date_Out ) AS Projected_Date, '
 	set @SQLString = @SQLString + N' ISNULL( datediff(day, slh.SL_Date_In, slh.SL_Date_Out ), pslt.PSLT_Cycle_Time_Normal ) AS Actual_Cycle_Time, '
 	set @SQLString = @SQLString + N' ISNULL( datediff(day, slh.SL_Date_In, slh.SL_Date_Out ), pslt.PSLT_Cycle_Time_Normal ) AS Projected_Cycle_Time, '
 	set @SQLString = @SQLString + N' pslt.PSLT_Cycle_Time_Normal AS Commited_Cycle_Time, slh.SL_wafer_Qty AS Qty_In , slh.SL_die_Qty_Out AS  Die_In, '
 	set @SQLString = @SQLString + N' slh.SL_Die_Qty_Out AS Qty_Out, ISNULL( slh.SL_Yield, 100.0 ) AS Actual_Yield, ISNULL( pslt.PSLT_Expected_Yield, 100.0 ) AS Commited_Yield, '
 	set @SQLString = @SQLString + N' 100 AS Target_Yield, v.v_name AS Vendor_Name, '
	set @SQLString = @SQLString + N' '''' AS Vendor_Lot '
 	set @SQLString = @SQLString + N' from Sort_lot_history slh , lot_table lt , '
 	set @SQLString = @SQLString + N' process_Step ps,  Location l, Process_Step_Location_Table pslt, Vendor V, product p '
 	set @SQLString = @SQLString + N' where slh.lot_id = lt.lot_id '
 	set @SQLString = @SQLString + N' and lt.processStep_id = ps.processStep_id '
 	set @SQLString = @SQLString + N' and lt.location_id = l.location_id '
 	set @SQLString = @SQLString + N' and lt.processStep_id = pslt.processStep_id '
 	set @SQLString = @SQLString + N' and lt.location_id = pslt.location_id '
 	set @SQLString = @SQLString + N' and L.Vendor_ID = V.Vendor_ID '
 	set @SQLString = @SQLString + N' and p.processStep_id = lt.processStep_id '
	set @SQLString = @SQLString + N' and Sl_Current_Status = ''Close'''
 	
 	if ( @vendor IS NOT NULL  )
 	begin
 	  set @SQLString = @SQLString + ' and V.V_Name = ''' +  @VENDOR + ''''
 	end
 	if ( @date_From IS NOT NULL AND ltrim(rtrim(@date_From)) <> '' AND @date_From <> '0' )
 	begin
 	  set @SQLString = @SQLString + ' and slh.SL_Date_In >= ''' +  @date_From + ''''
 	end
 	if ( @date_To IS NOT NULL AND ltrim(rtrim(@date_To)) <> '' AND @date_To <> '0' )
 	begin
 	  set @SQLString = @SQLString + ' and slh.SL_Date_Out <= ''' +  @date_To + ''''
 	end
 	if ( @productName IS NOT NULL AND ltrim(rtrim(@productName)) <> '' AND @productName <> '0' )
 	begin
 	  --set @SQLString = @SQLString + ' and p.part_Number =   ''' +  @productName + ''''
	    set @SQLString = @SQLString + ' and (SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
									CASE 
										WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
										WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
									END 
						)  = '''+ @productName +''''
 	end

	SET @SQLString = @SQLString + ' UNION '	
	
	set @SQLString1 = @SQLString1 + N' select  alh.lot_id AS Lot_ID, lt.lot_number AS Lot_Number, ps.PS_Type_Name AS Process_Type, ' 
 	set @SQLString1 = @SQLString1 + N' alh.AL_Date_In AS Date_In, alh.AL_Date_Out AS Date_Out, ISNULL( alh.AL_Expected_Date_Out, alh.AL_Date_Out ) AS Projected_Date, '
 	set @SQLString1 = @SQLString1 + N' ISNULL( datediff(day, alh.AL_Date_In, alh.AL_Date_Out ), pslt.PSLT_Cycle_Time_Normal ) AS Actual_Cycle_Time, '
 	set @SQLString1 = @SQLString1 + N' ISNULL( datediff(day, alh.AL_Date_In,alh.AL_Expected_Date_Out ), pslt.PSLT_Cycle_Time_Normal ) AS Projected_Cycle_Time, '
 	set @SQLString1 = @SQLString1 + N' pslt.PSLT_Cycle_Time_Normal AS Commited_Cycle_Time, alh.AL_Qty_In AS Qty_In , alh.AL_Qty_In AS  Die_In, '
 	set @SQLString1 = @SQLString1 + N' alh.AL_Qty_Out AS Qty_Out, ISNULL( alh.AL_Asy_Yield, 100.0 ) AS Actual_Yield, ISNULL( pslt.PSLT_Expected_Yield, 100.0 ) AS Commited_Yield, '
 	set @SQLString1 = @SQLString1 + N' 100 AS Target_Yield, v.v_name AS Vendor_Name, '
	set @SQLString1 = @SQLString1 + N' alh.AL_Vendor_Lot_Number AS Vendor_Lot '
 	set @SQLString1 = @SQLString1 + N' from assembly_lot_history Alh , lot_table lt , '
 	set @SQLString1 = @SQLString1 + N' process_Step ps,  Location l ,Process_Step_Location_Table pslt, Vendor V , product p '
 	set @SQLString1 = @SQLString1 + N' where alh.lot_id = lt.lot_id '
 	set @SQLString1 = @SQLString1 + N' and lt.processStep_id = ps.processStep_id '
 	set @SQLString1 = @SQLString1 + N' and lt.location_id = l.location_id '
 	set @SQLString1 = @SQLString1 + N' and lt.processStep_id = pslt.processStep_id '
 	set @SQLString1 = @SQLString1 + N' and lt.location_id = pslt.location_id '
 	set @SQLString1 = @SQLString1 + N' and L.Vendor_ID = V.Vendor_ID '
 	set @SQLString1 = @SQLString1 + N' and p.processStep_id = lt.processStep_id '
	set @SQLString1 = @SQLString1 + N' and al_Current_Status = ''Close'''
 	
 	if ( @vendor IS NOT NULL  )
 	begin
 	  set @SQLString1 = @SQLString1 + ' and V.V_Name =  ''' +  @VENDOR + ''''
 	end
 	if ( @date_From IS NOT NULL AND ltrim(rtrim(@date_From)) <> '' AND @date_From <> '0' )
 	begin
 	  set @SQLString1 = @SQLString1 + ' and alh.AL_Date_In >=  ''' +  @date_From + ''''
 	end
 	if ( @date_To IS NOT NULL AND ltrim(rtrim(@date_To)) <> '' AND @date_To <> '0' )
 	begin
 	  set @SQLString1 = @SQLString1 + ' and alh.aL_Date_Out <= ''' +  @date_To + ''''
 	end
 	if ( @productName IS NOT NULL AND ltrim(rtrim(@productName)) <> '' AND @productName <> '0' )
 	begin
 	--  set @SQLString = @SQLString + ' and p.part_Number =  ''' +  @productName + ''''
	 set @SQLString1 = @SQLString1 + ' and (SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
									CASE 
										WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
										WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
									END 
						)   = '''+ @productName +''''
 	end
 	 	
	SET @SQLString1 = @SQLString1 + N' UNION '	
	
 	set @SQLString1 = @SQLString1 + N' select  FTL.lot_id AS Lot_ID, lt.lot_number AS Lot_Number, ps.PS_Type_Name AS Process_Type, ' 
 	set @SQLString1 = @SQLString1 + N' FTL.FTL_Date_In AS Date_In, FTL.FTL_Date_Out AS Date_Out, ISNULL( FTL.FTL_Expected_Date,FTL.FTL_Date_Out)  AS Projected_Date, '
 	set @SQLString1 = @SQLString1 + N' ISNULL( datediff(day, FTL.FTL_Date_In, FTL.FTL_Date_Out ), pslt.PSLT_Cycle_Time_Normal ) AS Actual_Cycle_Time, '
 	set @SQLString1 = @SQLString1 + N' ISNULL( datediff(day, FTL.FTL_Date_In, FTL.FTL_Date_Out ), pslt.PSLT_Cycle_Time_Normal ) AS Projected_Cycle_Time, '
 	set @SQLString1 = @SQLString1 + N' pslt.PSLT_Cycle_Time_Normal AS Commited_Cycle_Time, FTL.FTL_Qty_In AS Qty_In , FTL.FTL_Qty_In AS  Die_In, '
 	set @SQLString1 = @SQLString1 + N' FTL.FTL_Qty_Out AS Qty_Out, ISNULL( FTL.FTL_Yield, 100.0 ) AS Actual_Yield, ISNULL( pslt.PSLT_Expected_Yield, 100.0 ) AS Commited_Yield, '
 	set @SQLString1 = @SQLString1 + N' 100 AS Target_Yield, v.v_name AS Vendor_Name, '
	set @SQLString1 = @SQLString1 + N' FTL.FTL_Vendor_Lot_Number AS Vendor_Lot '
 	set @SQLString1 = @SQLString1 + N' from Final_test_lot_history FTL , lot_table lt , '
 	set @SQLString1 = @SQLString1 + N' process_Step ps,  Location l ,Process_Step_Location_Table pslt, Vendor V , product p '
 	set @SQLString1 = @SQLString1 + N' where FTL.lot_id = lt.lot_id '
 	set @SQLString1 = @SQLString1 + N' and lt.processStep_id = ps.processStep_id '
 	set @SQLString1 = @SQLString1 + N' and lt.location_id = l.location_id '
 	set @SQLString1 = @SQLString1 + N' and lt.processStep_id = pslt.processStep_id '
 	set @SQLString1 = @SQLString1 + N' and lt.location_id = pslt.location_id '
 	set @SQLString1 = @SQLString1 + N' and L.Vendor_ID = V.Vendor_ID '
 	set @SQLString1 = @SQLString1 + N' and p.processStep_id = lt.processStep_id '
	set @SQLString1 = @SQLString1 + N' and FTL_Current_Status = ''Close'''
 	
 	if ( @vendor IS NOT NULL  )
 	begin
 	  set @SQLString1 = @SQLString1 + ' and V.V_Name =   ''' +  @VENDOR + ''''
 	end
 	if ( @date_From IS NOT NULL AND ltrim(rtrim(@date_From)) <> '' AND @date_From <> '0' )
 	begin
 	  set @SQLString1 = @SQLString1 + ' and FTL.FTL_Date_In >=  ''' +  @date_From + ''''
 	end
 	if ( @date_To IS NOT NULL AND ltrim(rtrim(@date_To)) <> '' AND @date_To <> '0' )
 	begin
 	  set @SQLString1 = @SQLString1 + ' and FTL.FTL_Date_Out <=  ''' +  @date_To + ''''
 	end
 	if ( @productName IS NOT NULL AND ltrim(rtrim(@productName)) <> '' AND @productName <> '0' )
 	begin
 	  --set @SQLString1 = @SQLString1 + ' and p.part_Number =   ''' +  @productName + ''''
	     set @SQLString1 = @SQLString1 + ' and (SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
									CASE 
										WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
										WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
									END 
						)   = '''+ @productName +''''
 	end
 	
	exec (@SQLString + @SQLString1 )
--print @SQLString
--print @SQLString1

end 